KexiDBDesign:

KexiDB versus QtSQL

 2005-02-01, J.Staniek, improved by Martin Ellis
 TODO: side-by-side code samples, graphs

Table of Contents      1. Introduction      2. Specific needs QtSQL doesn't offer      3. Other differences      4. KexiDB disadvantages      5. Other findings      6. Where to try KexiDB

1. Introduction

KexiDB is a database API that abstracts over the specifics of individual database backends. Using KexiDB, one can write high-level code that is truly database independent, due to it's built in SQL parser.

QtSQL is a database API distributed as part of Qt. It may fit your needs, but it does not satisfy the requirements for database designer app like Kexi.

This document provides you with analysis for KexiDB module vs QtSQL module. It's not intended to start flamewars, but to provide a comparison of their features, and show why QtSQL is not sufficient for Kexi. Unless otherwise stated, all comparisons will be made against QtSQL bundled with Qt4.

During reading, you can take a look at Kexi API documentation (look at classes within KexiDB namespace) and QtSQL module documentation.

Our hope is that KexiDB will be proposed as KDE Database Access API, and thus renamed to KDB or something like that.

2. Specific needs QtSQL doesn't offer

• 2.1. SQL Parser Submodule is built into KexiDB.

Kexi has an SQL Parser (used in SQL Designer View), to properly build SQL graphs (see http://www.kexi-project.org/pics/0.1beta3/relation_beta3.png) out of SQL statements (see http://www.kexi-project.org/pics/0.1beta5/sql_debugger.png).

The SQL parser is the key to writing database independent SQL. SQL statements in Kexi's SQL dialect are parsed, and then used to generate SQL in the dialect of the DB backend. This abstraction layer allows Kexi (and other programs using KexiDB) to use the same SQL staatements regardless the backends type system, identifier escaping syntax, keyword set, etc. Furthermore, SQL stored in queries within a database is in KexiDB's SQL dialect - so DBs can be migrated from one DB backend to another without the need to rewrite queries.

It's possible that this feature could be build on top of QtSQL, but QtSQL doesn't offer query schemas able to express full parse trees, so parser built on top of QtSQL would drop some information what is not acceptable. See 2.2.

• 2.2. Handling expressions within SQL queries. QtSQL module have no idea about real, detailed construction of each SQL query column. For example, with SQL query like "SELECT a*2 FROM table_b WHERE c>30": you need an expression classes for "*" and ">" operators, for more complex queries you need much more.

• 2.3. KexiDB maintains more meta-data about table and query structures.

Qt4 supports the idea of 'models', eg. QSqlQueryModel. However, KexiDB provides a way for maintaining full table/query schema meta-data. QtSQL glued query schema class with data container, while in KexiDB there are separate structures for these concepts.

For example query mentioned in 2.2, QtSQL will only rely on database engine where querying for column value of "a*2", so unless you're trying to ask your database sever what is the type of "a*2" column, QtSQL will not provide you any hint about the type. Apart from Kexi, there are many applications where knowledge of such a type could be valuable.

Maintaining meta-data about table structures is also invaluable when migrating between database backends.

• 2.4. Database transactions handling. QtSQL has only flat transactions support, while KexiDB can query database driver if it provides single/multiple/nested transactions. Further behaviour depends on capatibilities, eg. you can set autocommit for your selected part of your code.

• 2.5. Error handling. KexiDB offers more (translated) detailed error messages ready to use both in your GUI and command line applications (see http://www.iidea.pl/~js/kexi/shots/2004-02/server_message.png). QtSQL has QSqlError class while many KexiDB classes inherits from KexiDB::Object interface which provides an idea of status of the last operation. QtSQL has only top-level QSqlError::Type with few possible values while KexiDB has many more detailed constants useful for debugging purposes.

• 2.6. KexiDB has a more advanced data-aware widget framework

KexiDB has an associated widget set that provides familiar KDE-specific widgets (such as the Date/Time picker, see http://www.kexi-project.org/pics/0.1beta5/datetime_and_autonumber.png). It also supports complex GUI structures such as nested forms (http://www.kexi-project.org/pics/0.1beta5/subforms.png).

Once you have retrieved a (storable!) table or query schema, you can apply it to TableView or FormView object to immediately load it with database data. Continouous forms (see e.g. http://www.iidea.pl/~js/kexi/shots/cont_forms.png) and autoforms are also planned. With KexiDB, you can write less code to achieve the same or better effect with smaller debugging effort. Autonumbered columns work out of the box.

To create functional database-aware form with QtSQL's QSqlForm you need to write our own code using QSqlPropertyMap objects. While you can use QtDesigner to simplify this task, KFormDesigner provides KDE specific widgets - see 3.4.

KexiDB widgets provide support for KDE keyboard shortcuts.

3. Other differences

• 3.1. KexiDB uses KDE Services framework to query for, and find database drivers instead of using search paths declared within Qt/QtConfig and so on. (Although this means it is KDE specific, see KexiDB disadvantages below).

• 3.2. Development. Currently, KexiDB doesn't maintain binary compatibility from version to version. QtSQL version 3 maintains it, and version 4 is curently frozen in terms of API. Both approaches have advantages and drawbacks. If you prefer stability and simplified API for simple things, you probably like QtSQL more. OTOH, KexiDB won't stop adding features and fixing annoyances pointed by it's current usage.

• 3.3. Data/Schema Migraton Framework. KexiDB is reused in Kexi Migration Framework, something QtSQL lacks.

Because KexiDB supports rich database schema data storage, you are able to easier migrate schema and data from one database engine to another. Note: Importing native (not created using KexiDB) databases is in progress.

• 3.4. KexiDB features are is not integrated into QtDesigner. Kexi reuses componenets of KFormDesigner to offer Qt Designer replacement for KDE. This approach is considered to be more user friendly, even if developers are still using Qt Designer.

4. KexiDB disadvantages

• 4.1. KexiDB is KDE-only while QtSQL is for both Qt and KDE. This will change as soon as there is added a special KexiDB/Qt target/layer. Note: KexiDB usage is not limited to Unix environment, because it's actively used with kdelibs/win32 on MS Windows platform, for Kexi development.

• 4.2. KexiDB has fewer database drivers than QtSQL. See: KexiDB Drivers. You're free to help us with database driver development.

5. Other findings

• 5.1. If you really want, QtSQL drivers can be wrapped to be KexiDB compatible. This cannot be performed in the opposite way, unless you're want to strip out some functionality from KexiDB. Note that you'll lose independence from the actual backend database dialect though. :o(

• 5.2. What about QtSQL module in Qt4? No doubt, it's improved, but note that current KexiDB design started in mid 2003, i.e. two years before Qt4 stable is planned. In 2003 we couldn't waiting two years for something even not yet announced, as it's important module (at least from Kexi development perspective).

• 5.3. Applications using KexiDB (except Kexi, and future versions of other KOffice apps):
  • ShowImg: KexiDB library is used to provide image database. The goal of this database is, as usual for this kind of feature..., to tag images, and search images using keywords, dates, etc.

Screenshots are available here: http://ric.jalix.org/screenshots/showimg-0.9.5-20041228.png, http://ric.jalix.org/screenshots/showimg-0.9.5-20050121.png, http://ric.jalix.org/screenshots/showimg-0.9.5-20050103.png. You can test it grabbing CVS version. (thanks to Richard Groult)

• 5.4. For other QtSQL replacements, see:
  • Rekall 's libraries,
  • hk_classes from KNoda

6. Where to try KexiDB

Ok, you convinced me a bit, what next?

Download Kexi, compile and install it. Then and go to kexi/tests/newapi/ directory and compile it. These tests are quite good demonstration, splitted up to several .h files included by main.cpp.